from typing import List, Dict, Any, Optional
from dataclasses import dataclass
from enum import Enum
import sys
import os

# 导入数据库连接模块
sys.path.append(os.path.dirname(os.path.abspath(__file__)))
from database_connection import DatabaseManager, DatabaseConfig, DatabaseType


class ColumnType(Enum):
    """数据库列类型枚举"""
    INTEGER = "INTEGER"
    TEXT = "TEXT"
    REAL = "REAL"
    BLOB = "BLOB"
    VARCHAR = "VARCHAR"
    CHAR = "CHAR"
    BOOLEAN = "BOOLEAN"
    DATE = "DATE"
    DATETIME = "DATETIME"
    TIMESTAMP = "TIMESTAMP"


@dataclass
class ColumnInfo:
    """列信息数据类"""
    name: str
    data_type: str
    is_nullable: bool = True
    is_primary_key: bool = False
    is_unique: bool = False
    default_value: Optional[str] = None
    max_length: Optional[int] = None
    is_auto_increment: bool = False


@dataclass
class TableInfo:
    """表信息数据类"""
    name: str
    columns: List[ColumnInfo]
    row_count: int = 0
    indexes: List[str] = None
    foreign_keys: List[Dict] = None


class TableManager:
    """表结构管理器"""

    def __init__(self, db_manager: DatabaseManager):
        self.db_manager = db_manager

    def get_table_info(self, table_name: str) -> Optional[TableInfo]:
        """获取表的详细信息"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            columns = self._get_table_columns(table_name)
            row_count = self._get_table_row_count(table_name)
            indexes = self._get_table_indexes(table_name)
            foreign_keys = self._get_table_foreign_keys(table_name)

            return TableInfo(
                name=table_name,
                columns=columns,
                row_count=row_count,
                indexes=indexes,
                foreign_keys=foreign_keys
            )

        except Exception as e:
            print(f"获取表信息失败: {str(e)}")
            return None

    def _get_table_columns(self, table_name: str) -> List[ColumnInfo]:
        """获取表的列信息"""
        columns = []

        if self.db_manager.config.db_type == DatabaseType.SQLITE:
            # SQLite的PRAGMA table_info查询
            query = f"PRAGMA table_info({table_name})"
            results = self.db_manager.execute_query(query)

            for row in results:
                column = ColumnInfo(
                    name=row['name'],
                    data_type=row['type'],
                    is_nullable=not bool(row['notnull']),
                    is_primary_key=bool(row['pk']),
                    default_value=row['dflt_value']
                )
                columns.append(column)

        elif self.db_manager.config.db_type == DatabaseType.MYSQL:
            # MySQL的DESCRIBE查询
            query = f"DESCRIBE {table_name}"
            results = self.db_manager.execute_query(query)

            for row in results:
                column = ColumnInfo(
                    name=row['Field'],
                    data_type=row['Type'],
                    is_nullable=row['Null'] == 'YES',
                    is_primary_key=row['Key'] == 'PRI',
                    is_unique=row['Key'] == 'UNI',
                    default_value=row['Default']
                )
                columns.append(column)

        elif self.db_manager.config.db_type == DatabaseType.POSTGRESQL:
            # PostgreSQL的information_schema查询
            query = """
            SELECT column_name, data_type, is_nullable, column_default
            FROM information_schema.columns 
            WHERE table_name = %s
            ORDER BY ordinal_position
            """
            results = self.db_manager.execute_query(query, (table_name,))

            for row in results:
                column = ColumnInfo(
                    name=row['column_name'],
                    data_type=row['data_type'],
                    is_nullable=row['is_nullable'] == 'YES',
                    default_value=row['column_default']
                )
                columns.append(column)

        return columns

    def _get_table_row_count(self, table_name: str) -> int:
        """获取表的行数"""
        try:
            query = f"SELECT COUNT(*) as count FROM {table_name}"
            result = self.db_manager.execute_query(query)
            return result[0]['count'] if result else 0
        except:
            return 0

    def _get_table_indexes(self, table_name: str) -> List[str]:
        """获取表的索引信息"""
        indexes = []

        try:
            if self.db_manager.config.db_type == DatabaseType.SQLITE:
                query = f"PRAGMA index_list({table_name})"
                results = self.db_manager.execute_query(query)
                indexes = [row['name'] for row in results]

            elif self.db_manager.config.db_type == DatabaseType.MYSQL:
                query = f"SHOW INDEX FROM {table_name}"
                results = self.db_manager.execute_query(query)
                indexes = list(set([row['Key_name'] for row in results if row['Key_name'] != 'PRIMARY']))

        except:
            pass

        return indexes

    def _get_table_foreign_keys(self, table_name: str) -> List[Dict]:
        """获取表的外键信息"""
        foreign_keys = []

        try:
            if self.db_manager.config.db_type == DatabaseType.SQLITE:
                query = f"PRAGMA foreign_key_list({table_name})"
                results = self.db_manager.execute_query(query)

                for row in results:
                    foreign_keys.append({
                        'column': row['from'],
                        'referenced_table': row['table'],
                        'referenced_column': row['to']
                    })

        except:
            pass

        return foreign_keys

    def create_table(self, table_name: str, columns: List[ColumnInfo]) -> bool:
        """创建新表"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            # 构建CREATE TABLE语句
            column_definitions = []

            for col in columns:
                definition = f"{col.name} {col.data_type}"

                if col.max_length and col.data_type.upper() in ['VARCHAR', 'CHAR']:
                    definition = f"{col.name} {col.data_type}({col.max_length})"

                if col.is_primary_key:
                    definition += " PRIMARY KEY"

                if col.is_auto_increment and self.db_manager.config.db_type == DatabaseType.SQLITE:
                    definition += " AUTOINCREMENT"
                elif col.is_auto_increment and self.db_manager.config.db_type == DatabaseType.MYSQL:
                    definition += " AUTO_INCREMENT"

                if not col.is_nullable:
                    definition += " NOT NULL"

                if col.is_unique and not col.is_primary_key:
                    definition += " UNIQUE"

                if col.default_value:
                    definition += f" DEFAULT {col.default_value}"

                column_definitions.append(definition)

            create_sql = f"CREATE TABLE {table_name} ({', '.join(column_definitions)})"

            self.db_manager.execute_non_query(create_sql)
            print(f"表 '{table_name}' 创建成功")
            return True

        except Exception as e:
            print(f"创建表失败: {str(e)}")
            return False

    def drop_table(self, table_name: str) -> bool:
        """删除表"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            drop_sql = f"DROP TABLE IF EXISTS {table_name}"
            self.db_manager.execute_non_query(drop_sql)
            print(f"表 '{table_name}' 删除成功")
            return True

        except Exception as e:
            print(f"删除表失败: {str(e)}")
            return False

    def add_column(self, table_name: str, column: ColumnInfo) -> bool:
        """向表中添加列"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            definition = f"{column.name} {column.data_type}"

            if column.max_length and column.data_type.upper() in ['VARCHAR', 'CHAR']:
                definition = f"{column.name} {column.data_type}({column.max_length})"

            if not column.is_nullable:
                definition += " NOT NULL"

            if column.default_value:
                definition += f" DEFAULT {column.default_value}"

            alter_sql = f"ALTER TABLE {table_name} ADD COLUMN {definition}"
            self.db_manager.execute_non_query(alter_sql)
            print(f"列 '{column.name}' 添加成功")
            return True

        except Exception as e:
            print(f"添加列失败: {str(e)}")
            return False

    def drop_column(self, table_name: str, column_name: str) -> bool:
        """从表中删除列"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            if self.db_manager.config.db_type == DatabaseType.SQLITE:
                print("SQLite不支持直接删除列，需要重建表")
                return False

            alter_sql = f"ALTER TABLE {table_name} DROP COLUMN {column_name}"
            self.db_manager.execute_non_query(alter_sql)
            print(f"列 '{column_name}' 删除成功")
            return True

        except Exception as e:
            print(f"删除列失败: {str(e)}")
            return False

    def create_index(self, table_name: str, index_name: str, columns: List[str], unique: bool = False) -> bool:
        """创建索引"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            unique_keyword = "UNIQUE " if unique else ""
            columns_str = ", ".join(columns)

            create_index_sql = f"CREATE {unique_keyword}INDEX {index_name} ON {table_name} ({columns_str})"
            self.db_manager.execute_non_query(create_index_sql)
            print(f"索引 '{index_name}' 创建成功")
            return True

        except Exception as e:
            print(f"创建索引失败: {str(e)}")
            return False

    def drop_index(self, index_name: str, table_name: str = None) -> bool:
        """删除索引"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            if self.db_manager.config.db_type == DatabaseType.SQLITE:
                drop_sql = f"DROP INDEX IF EXISTS {index_name}"
            elif self.db_manager.config.db_type == DatabaseType.MYSQL:
                drop_sql = f"DROP INDEX {index_name} ON {table_name}"
            else:
                drop_sql = f"DROP INDEX IF EXISTS {index_name}"

            self.db_manager.execute_non_query(drop_sql)
            print(f"索引 '{index_name}' 删除成功")
            return True

        except Exception as e:
            print(f"删除索引失败: {str(e)}")
            return False

    def print_table_info(self, table_name: str):
        """打印表的详细信息"""
        table_info = self.get_table_info(table_name)

        if not table_info:
            print(f"无法获取表 '{table_name}' 的信息")
            return

        print(f"\n表名: {table_info.name}")
        print(f"行数: {table_info.row_count}")
        print("-" * 60)

        # 打印列信息
        print("列信息:")
        print(f"{'列名':<20} {'类型':<15} {'可空':<6} {'主键':<6} {'唯一':<6} {'默认值':<10}")
        print("-" * 60)

        for col in table_info.columns:
            nullable = "是" if col.is_nullable else "否"
            primary = "是" if col.is_primary_key else "否"
            unique = "是" if col.is_unique else "否"
            default = col.default_value or ""

            print(f"{col.name:<20} {col.data_type:<15} {nullable:<6} {primary:<6} {unique:<6} {default:<10}")

        # 打印索引信息
        if table_info.indexes:
            print(f"\n索引: {', '.join(table_info.indexes)}")

        # 打印外键信息
        if table_info.foreign_keys:
            print("\n外键:")
            for fk in table_info.foreign_keys:
                print(f"  {fk['column']} -> {fk['referenced_table']}.{fk['referenced_column']}")


# 演示和测试代码
if __name__ == "__main__":
    print("数据库管理工具 - 表结构管理模块测试")
    print("=" * 50)

    # 创建数据库连接
    from database_connection import DatabaseConfig, DatabaseType

    db_manager = DatabaseManager()
    config = DatabaseConfig(
        db_type=DatabaseType.SQLITE,
        database="test_database.db"
    )

    if db_manager.connect(config):
        table_manager = TableManager(db_manager)

        # 测试创建表
        print("\n创建测试表...")
        columns = [
            ColumnInfo(
                name="id",
                data_type="INTEGER",
                is_primary_key=True,
                is_auto_increment=True,
                is_nullable=False
            ),
            ColumnInfo(
                name="name",
                data_type="VARCHAR",
                max_length=100,
                is_nullable=False
            ),
            ColumnInfo(
                name="email",
                data_type="VARCHAR",
                max_length=255,
                is_unique=True,
                is_nullable=False
            ),
            ColumnInfo(
                name="age",
                data_type="INTEGER",
                default_value="0"
            ),
            ColumnInfo(
                name="created_at",
                data_type="DATETIME",
                default_value="CURRENT_TIMESTAMP"
            )
        ]

        table_manager.create_table("employees", columns)

        # 插入一些测试数据
        print("\n插入测试数据...")
        insert_sql = "INSERT INTO employees (name, email, age) VALUES (?, ?, ?)"
        db_manager.execute_non_query(insert_sql, ("张三", "zhangsan@company.com", 25))
        db_manager.execute_non_query(insert_sql, ("李四", "lisi@company.com", 30))
        db_manager.execute_non_query(insert_sql, ("王五", "wangwu@company.com", 28))

        # 显示表信息
        print("\n表结构信息:")
        table_manager.print_table_info("employees")

        # 测试添加列
        print("\n添加新列...")
        new_column = ColumnInfo(
            name="department",
            data_type="VARCHAR",
            max_length=50,
            default_value="'未分配'"
        )
        table_manager.add_column("employees", new_column)

        # 创建索引
        print("\n创建索引...")
        table_manager.create_index("employees", "idx_email", ["email"], unique=True)
        table_manager.create_index("employees", "idx_name_age", ["name", "age"])

        # 再次显示表信息
        print("\n更新后的表结构信息:")
        table_manager.print_table_info("employees")

        # 显示所有表
        print("\n数据库中的所有表:")
        tables = db_manager.get_tables()
        for table in tables:
            print(f"  - {table}")

        db_manager.disconnect()

    print("\n表结构管理模块测试完成！")
